﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.IO;
using System.Reflection;
using System.Windows.Forms;
using Extensibility;
using EnvDTE;
using EnvDTE80;
using System.CodeDom.Compiler;
using CoNatural.Data.VisualStudio.AddIn.Tools;

namespace CoNatural.Data.VisualStudio.AddIn.Controls {
   public partial class CommandPreviewControl : UserControl {
      private string connectionString;
      private Model.Parameters commandParameters;
		private string _namespace;
		private string name;
      private AppDomain tmpDomain;
      private CommandLoader cmdLoader;
		private string[] commands;

      public CommandPreviewControl() {
         InitializeComponent();
         AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);
      }

      /// <summary> 
      /// Clean up any resources being used.
      /// </summary>
      /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
      protected override void Dispose(bool disposing) {
         if (disposing) {
            if (tmpDomain != null)
               AppDomain.Unload(tmpDomain);

            if (components != null) 
               components.Dispose();
         }
         base.Dispose(disposing);
      }

      private Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args) {
         // hack to load CoNatural.Data... assemblies from addin directory
         if (args.Name.StartsWith("CoNatural.Data.VisualStudio.AddIn"))
            return Assembly.LoadFrom(typeof(Tools.CommandLoader).Assembly.Location);
         else if (args.Name.StartsWith("CoNatural.Data"))
            return Assembly.LoadFrom(typeof(CoNatural.Data.ParameterAttribute).Assembly.Location);
         return null;
      }

      public void Initialize(string connectionString, ProjectItem item) {
			toolExecute.Enabled = false;
         toolDeploy.Enabled = false;
			toolReaderModel.Enabled = false;
			toolParameterModel.Enabled = false;

			try {
            this.connectionString = connectionString;

            string csPath = item.Properties.Item("FullPath").Value.ToString();
            csTextBox.Text = File.ReadAllText(csPath);

				_namespace = item.ContainingProject.Name + ".Model"; // Helpers.ProjectItemHelper.GetNamespace(item, false);
				name = Path.GetFileNameWithoutExtension(csPath);

            // create command loader in tmp app domain
            cmdLoader = CreateLoader(item);

				// get list of project referenced assemblies 
				List<string> references = new List<string>();
            VSLangProj.VSProject project = item.ContainingProject.Object as VSLangProj.VSProject;
            foreach (VSLangProj.Reference reference in project.References)
               if (reference.Type == VSLangProj.prjReferenceType.prjReferenceTypeAssembly)
						if(!string.IsNullOrEmpty(reference.Path))
							// skip reference to mscorlib
							if(reference.Name != typeof(int).Assembly.GetName().Name)
								references.Add(reference.Path);
				
				// load commands from file
				cmdLoader.LoadCommands(csPath, csTextBox.Text, references.ToArray());
				commands = cmdLoader.Commands;

				// load first command
				if (commands != null) {
					commandsComboBox.Items.AddRange(commands);
					commandsComboBox.SelectedIndex = 0;
				}
         }
         catch (Exception ex) {
            DisplayMsg(ex.Message);
         }
      }

		private void LoadCommand(int index) {
			// get SqlCommand to initialize control
			SqlCommand sqlCmd = cmdLoader.ToSqlCommand(index);
			sqlTextBox.Text = sqlCmd.CommandText;

			// copy parameters to main AppDomain
			commandParameters = new Model.Parameters();
			foreach (SqlParameter parameter in sqlCmd.Parameters) {
				SqlParameter p = new SqlParameter();
				p.ParameterName = parameter.ParameterName;
				p.SqlDbType = parameter.SqlDbType;
				p.UdtTypeName = parameter.UdtTypeName;
				p.Direction = parameter.Direction;
				p.Size = parameter.Size;
				p.Precision = parameter.Precision;
				p.Scale = parameter.Scale;
				p.IsNullable = parameter.IsNullable;
				if (p.Direction != ParameterDirection.Output)
					commandParameters.InputParameters.Add(new Model.Parameter(p));
				if (p.Direction != ParameterDirection.Input)
					commandParameters.OutputParameters.Add(new Model.Parameter(p));
			}

			// bind parameters
			inputParametersBindingSource.DataSource = commandParameters.InputParameters;
			outputParametersBindingSource.DataSource = commandParameters.OutputParameters;

			// enable tools
			toolExecute.Enabled = true;
			toolDeploy.Enabled = true;
			if (commandParameters.InputParameters.Count > 0 || commandParameters.OutputParameters.Count > 0)
				toolParameterModel.Enabled = true;
		}

      private CommandLoader CreateLoader(ProjectItem item) {
         VSLangProj80.ProjectConfigurationProperties3 configProperties = item.ContainingProject.ConfigurationManager.ActiveConfiguration.Object as VSLangProj80.ProjectConfigurationProperties3;
         string basePath = Path.Combine(Path.GetDirectoryName(item.ContainingProject.FullName), configProperties.OutputPath);
         string location = typeof(CommandLoader).Assembly.Location;
         tmpDomain = AppDomain.CreateDomain("TemporaryCommandDomain", null, basePath, null, false);
         return (CommandLoader)tmpDomain.CreateInstanceFromAndUnwrap(location, "CoNatural.Data.VisualStudio.AddIn.Tools.CommandLoader");
      }

      private void DisplayMsg(string error) {
         TextBox errors = new TextBox();
         errors.ReadOnly = true;
         errors.Multiline = true;
         errors.ScrollBars = ScrollBars.Both;
         errors.Name = "Results";
         errors.Text = error;

         mainLayoutPanel.Controls.RemoveByKey("Results");
         mainLayoutPanel.Controls.Add(errors, 0, 3);
         errors.Dock = DockStyle.Fill;
      }

		private void toolExecute_Click(object sender, EventArgs e) {
			toolReaderModel.Enabled = false;
			SqlCommand cmd = null;
			try {
				// accept parameter values
				this.inputDataGridView.EndEdit();

				using(SqlConnection conn = new SqlConnection(connectionString)) {
					cmd = new SqlCommand(sqlTextBox.Text, conn);
					foreach(Model.Parameter p in inputParametersBindingSource) {
						cmd.Parameters.Add(p.DbParameter);
					}
					foreach(Model.Parameter p in outputParametersBindingSource) {
						if(!cmd.Parameters.Contains(p.Name)) {
							cmd.Parameters.Add(p.DbParameter);
						}
					}
					using(SqlDataAdapter adapter = new SqlDataAdapter(cmd)) {
						DataSet ds = new DataSet();
						adapter.Fill(ds);

						// update output parameters
						this.outputDataGridView.Refresh();

						// show results
						if(ds.Tables.Count > 0) {
							DataGridView dgv = new DataGridView();
							dgv.ReadOnly = true;
							dgv.ScrollBars = ScrollBars.Both;
							dgv.Name = "Results";
							dgv.RowHeadersVisible = false;
							dgv.AllowDrop = false;
							dgv.AllowUserToAddRows = false;
							dgv.AllowUserToDeleteRows = false;
							dgv.AllowUserToOrderColumns = false;
							dgv.AutoSize = true;
							dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
							dgv.DataSource = ds.Tables[0];

							mainLayoutPanel.Controls.RemoveByKey("Results");
							mainLayoutPanel.Controls.Add(dgv, 0, 3);
							dgv.Dock = DockStyle.Fill;

							// enable button to generate model from results
							toolReaderModel.Enabled = true;
						}
						else
							DisplayMsg("Command executed with no results.");
					}
				}
			}
			catch(Exception ex) {
				DisplayMsg(ex.Message);
			}
			finally {
				if(cmd != null)
					cmd.Parameters.Clear();
			}
		}

		private void toolReaderModel_Click(object sender, EventArgs e) {
			DataGridView dgv = mainLayoutPanel.Controls["Results"] as DataGridView;
			if(dgv != null) {
				DataTable table = dgv.DataSource as DataTable;
				if (table != null && table.Columns.Count > 0) {
					var properties = new List<CoNatural.Data.Helpers.PropertyHelper>();
					foreach (DataColumn column in table.Columns)
						properties.Add(new CoNatural.Data.Helpers.PropertyHelper(column));
					
					var sb = new StringBuilder();
					sb.AppendLine(@"// MODEL");
					sb.AppendLine(Helpers.ModelBuilder.BuildModel(_namespace, "Model", properties));

					using (Forms.ModelViewForm f = new Forms.ModelViewForm(name)) {
						f.ModelText = sb.ToString();
						f.ShowDialog();
					}
					return;
				}
			}

			MessageBox.Show("Cannot generate model from results.");
		}

		private void toolParameterModel_Click(object sender, EventArgs e) {
			if (commandParameters.InputParameters.Count > 0 || commandParameters.OutputParameters.Count > 0) {
				var properties = new Dictionary<string, CoNatural.Data.Helpers.PropertyHelper>();
				var inputParameters = new List<CoNatural.Data.Helpers.PropertyHelper>();
				var outputParameters = new List<CoNatural.Data.Helpers.PropertyHelper>();
				foreach (var parameter in commandParameters.InputParameters) {
					var p = new CoNatural.Data.Helpers.PropertyHelper(parameter.DbParameter);
					properties.Add(p.Name, p);
					inputParameters.Add(p);
				}
				foreach (var parameter in commandParameters.OutputParameters) {
					var p = new CoNatural.Data.Helpers.PropertyHelper(parameter.DbParameter);
					if (!properties.ContainsKey(p.Name))
						properties.Add(p.Name, p);
					outputParameters.Add(p);
				}

				var sb = new StringBuilder();
				sb.AppendLine(@"// MODEL");
				sb.AppendLine(Helpers.ModelBuilder.BuildModel(_namespace, "Model", properties.Values));

				using (Forms.ModelViewForm f = new Forms.ModelViewForm(name)) {
					f.ModelText = sb.ToString();
					f.ShowDialog();
				}
				return;
			}

			MessageBox.Show("Cannot generate model from parameters");
		}

      private void toolDeploy_Click(object sender, EventArgs e) {
         try {
				if (commandsComboBox.SelectedIndex >= 0 && commandsComboBox.SelectedIndex < commands.Length) {
					cmdLoader.Deploy(connectionString, commandsComboBox.SelectedIndex);
					DisplayMsg("Command deployed successfully.");
				}
         }
         catch (Exception ex) {
            DisplayMsg(ex.Message);
         }
      }

		private void commandsComboBox_SelectedIndexChanged(object sender, EventArgs e) {
			LoadCommand(commandsComboBox.SelectedIndex);
			DisplayMsg(string.Format("Command {0} loaded.", commands[commandsComboBox.SelectedIndex]));
		}
   }
}
